package com.gis.mapper;

import com.gis.dto.WaterQualityReportDTO;

import com.gis.dto.YearAndSeasonDTO;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.gis.entity.WaterQualityData;

import java.util.List;

@Mapper
@Repository
public interface WaterQualityDataMapper extends BaseMapper<WaterQualityData> {
    @Select("SELECT " +
            "wq.station_id, " +
            "wq.tide_status, " +
            "s.name, " +
            "s.latitude, " +
            "s.longitude, " +
            "COALESCE(AVG(wq.ph), -1.0) AS ph, " +
            "COALESCE(AVG(wq.dissolved_oxygen), -1.0) AS dissolved_oxygen, " +
            "COALESCE(AVG(wq.cod), -1.0) AS cod, " +
            "COALESCE(AVG(wq.n), -1.0) AS n, " +
            "COALESCE(AVG(wq.po_4), -1.0) AS po4, " +
            "COALESCE(AVG(wq.petroleum_class), -1.0) AS petroleum_class, " +
            "COALESCE(AVG(wq.copper), -1.0) AS copper, " +
            "COALESCE(AVG(wq.zinc), -1.0) AS zinc, " +
            "COALESCE(AVG(wq.lead), -1.0) AS lead, " +
            "COALESCE(AVG(wq.cadmium), -1.0) AS cadmium, " +
            "COALESCE(AVG(wq.mercury), -1.0) AS mercury, " +
            "COALESCE(AVG(wq.arsenic), -1.0) AS arsenic, " +
            "COALESCE(AVG(wq.chromium), -1.0) AS chromium, " +
            "COALESCE(AVG(wq.sulfide), -1.0) AS sulfide, " +
            "COALESCE(AVG(wq.voc), -1.0) AS voc " +
            "FROM water_quality_data wq " +
            "JOIN station_data s ON wq.station_id = s.id " +
            "WHERE wq.year = #{year} AND wq.season = #{season} " +
            "GROUP BY wq.station_id, wq.tide_status, s.name, s.latitude, s.longitude " +
            "ORDER BY s.name")
    List<WaterQualityReportDTO> getWaterQualityAvgByYearAndSeason(int year, String season);

    @Select("SELECT * " +
            "FROM water_quality_data " +
            "WHERE station_id = #{id} " +
            "ORDER BY year DESC, " +
            "         CASE " +
            "             WHEN season = 'spring' THEN 1 " +
            "             WHEN season = 'summer' THEN 2 " +
            "             WHEN season = 'fall' THEN 3 " +
            "             WHEN season = 'winter' THEN 4 " +
            "             ELSE 5 " +
            "         END DESC " +
            "LIMIT #{n}")
    List<WaterQualityData> getLastN(@Param("n") int n, @Param("id") int id);

    @Select("SELECT * " +
            "FROM water_quality_data " +
            "WHERE station_id = #{id} " +
            "ORDER BY year DESC, " +
            "         CASE " +
            "             WHEN season = 'spring' THEN 1 " +
            "             WHEN season = 'summer' THEN 2 " +
            "             WHEN season = 'fall' THEN 3 " +
            "             WHEN season = 'winter' THEN 4 " +
            "             ELSE 5 " +
            "         END DESC " +
            "LIMIT 1")
    WaterQualityData LatestYearAndSeason(@Param("id") int id);


    @Select("SELECT * " +
            "FROM water_quality_data " +
            "WHERE station_id = #{id} " +
            "AND (year >= #{fromYear} AND year <= #{toYear}) " +
            "AND tide_status = #{tideStatus} " +
            "ORDER BY year ASC, " +
            "         CASE " +
            "             WHEN season = 'spring' THEN 4 " +
            "             WHEN season = 'summer' THEN 3 " +
            "             WHEN season = 'fall' THEN 2 " +
            "             WHEN season = 'winter' THEN 1 " +
            "             ELSE 5 " +
            "         END DESC")
    List<WaterQualityData> getFromYear(@Param("id") int id, @Param("fromYear") int fromYear, @Param("toYear") int toYear, @Param("tideStatus") String tideStatus);
}
